﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DTO;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
using System.Threading;


namespace DAO
{
    public class KhachHangDAO
    {
        public List<KhachHangDTO> LayDanhSachKhachHang()
        {
            List<KhachHangDTO> listKH = new List<KhachHangDTO>();
            SqlConnection conn = null;
            SqlDataReader reader = null;
            try
            {
                String sql = "Select * from KhachHang";

                conn = DataProvider2.ConnectDB();
                SqlCommand cm = new SqlCommand(sql, conn);
                reader = cm.ExecuteReader();
                while (reader.Read())
                {
                    KhachHangDTO khDTO = new KhachHangDTO();
                    khDTO.ID_KhachHang = reader.GetInt32(0);
                    khDTO.TenKhachHang = reader.GetString(1);
                    khDTO.DienThoai = reader.GetString(2);
                    khDTO.Email = reader.GetString(3);
                    khDTO.DiaChi = reader.GetString(4);
                    khDTO.CongTy = reader.GetString(5);

                    listKH.Add(khDTO);
                }
            }
            catch (Exception ex)
            { String e = ex.ToString(); }
            finally
            { conn.Close(); }
            return listKH;
        }
        public List<KhachHangDTO> LayDanhSachKhachHangTheoMa(int makh)
        {
            List<KhachHangDTO> listKH = new List<KhachHangDTO>();
            SqlConnection conn = null;
            SqlDataReader reader = null;
            try
            {
                String sql = "Select * from KhachHang Where ID_KhachHang = " + makh;

                conn = DataProvider2.ConnectDB();
                SqlCommand cm = new SqlCommand(sql, conn);
                reader = cm.ExecuteReader();
                while (reader.Read())
                {
                    KhachHangDTO khDTO = new KhachHangDTO();
                    khDTO.ID_KhachHang= reader.GetInt32(0);
                    khDTO.TenKhachHang = reader.GetString(1);
                    khDTO.DienThoai = reader.GetString(2);
                    khDTO.Email = reader.GetString(3);
                    khDTO.DiaChi = reader.GetString(4);
                    khDTO.CongTy = reader.GetString(5);
                    
                    listKH.Add(khDTO);
                }
            }
            catch (Exception ex)
            { String e = ex.ToString(); }
            finally
            { conn.Close(); }
            return listKH;
        }
        public KhachHangDTO LayDanhSachKhachHangTheoMa2(int ID_KhachHang)
        {
            KhachHangDTO khDTO = new KhachHangDTO();
            SqlConnection conn = null;
            SqlDataReader reader = null;
            try
            {
                conn = DataProvider2.ConnectDB();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_MucUngDung_XemTTKH";
                SqlParameter para;
                para = cmd.Parameters.Add("@ID_KhachHang", SqlDbType.Int);
                para.Value = ID_KhachHang;
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    khDTO.ID_KhachHang = reader.GetInt32(0);
                    khDTO.TenKhachHang = reader.GetString(1);
                    khDTO.DienThoai = reader.GetString(2);
                    khDTO.Email = reader.GetString(3);
                    khDTO.DiaChi = reader.GetString(4);
                    khDTO.CongTy = reader.GetString(4);
                }
                reader.Close();
            }
            catch (SqlException)
            {
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return khDTO;
        }
        public string ThemKhachHang(KhachHangDTO khDTO)
        { 
            string msg = "";
            SqlConnection conn = null;
            try
            {
                conn = DataProvider2.ConnectDB();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_MucUngDung_ThemKhachHang";
                SqlParameter para;
                para = cmd.Parameters.Add("@TenKhachHang", SqlDbType.NVarChar);
                para.Value = khDTO.TenKhachHang;
                para = cmd.Parameters.Add("@DienThoai", SqlDbType.NVarChar);
                para.Value = khDTO.DienThoai;
                para = cmd.Parameters.Add("@Email", SqlDbType.NVarChar);
                para.Value = khDTO.Email;
                para = cmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar);
                para.Value = khDTO.DiaChi;
                para = cmd.Parameters.Add("@CongTy", SqlDbType.NVarChar);
                para.Value = khDTO.CongTy;
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                msg = ex.Message;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return msg;
        }

    }
}
